#!/usr/bin/env python3
# coding: utf-8
"""
mysql 异步版本
"""
import traceback
import logging
import aiomysql
import asyncio
import time

logobj = logging.getLogger('mysql')

class Think(object):

    def __init__(self):
        self.coon = None
        self.pool = None

    # 创建连接
    async def create_pool(self):
        try:
            logobj.debug("will connect mysql~")
            __pool = await aiomysql.create_pool(
                minsize=5,  # 连接池最小值
                maxsize=10,  # 连接池最大值
                host='127.0.0.1',
                port=3306,
                user='root',
                password='root',
                db='mysql',
                autocommit=True,  # 自动提交模式
            )
            # self.pool = __pool
            return __pool
        except:
            logobj.error('connect error.', exc_info=True)

    # 获取游标
    async def getCurosr(self):
        conn = await self.pool.acquire()
        # 返回字典格式
        cur = await conn.cursor(aiomysql.DictCursor)
        return conn, cur

    async def get_db(self):
        self.pool = await self.create_pool()
        return self

    """
    查询操作
    :param query: sql语句
    :param param: 参数
    :return:
    """
    async def query(self, query, param=None):
        conn, cur = await self.getCurosr()

        try:
            await cur.execute(query, param)
            return await cur.fetchall()
        except:
            logobj.error(traceback.format_exc())
        finally:
            if cur:
                await cur.close()
            # 释放掉conn,将连接放回到连接池中
            await self.pool.release(conn)

    """
    增删改 操作
    :param query: sql语句
    :param param: 参数
    :return:
    """
    async def execute(self, query, param=None):
        conn, cur = await self.getCurosr()
        try:
            await cur.execute(query, param)
            if cur.rowcount == 0:
                return False
            else:
                return True
        except:
            logobj.error(traceback.format_exc())
        finally:
            if cur:
                await cur.close()
            # 释放掉conn,将连接放回到连接池中
            await self.pool.release(conn)

# 调用方
async def main():
    # 把所有任务添加到task中
    tasks = [
        test_select()
    ]
    await asyncio.wait(tasks)

async def test_select():
    db = await Think().get_db()
    res = await db.query("SELECT Host,User FROM user")
    # res = await db.query("SELECT Host,User FROM user")
    print("查询结果", res)


if __name__ == '__main__':
    start = time.time()
    # 创建一个事件循环对象loop
    loop = asyncio.get_event_loop()
    try:
        # 完成事件循环，直到最后一个任务结束
        loop.run_until_complete(main())
    finally:
        loop.close()  # 结束事件循环
    print('任务总耗时%.5f秒' % float(time.time() - start))